import top.malaoshi.data.*;
import top.malaoshi.util.Out;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class StdGroupBy聚合函数 {


    static DataTable createTable(){
        DataTable dt = new DataTable("t_user");

        dt.columns().add("id");
        dt.columns().add("username", DataColumn.Type.TEXT);
        dt.columns().add("password", DataColumn.Type.TEXT);
        dt.columns().add("age", DataColumn.Type.INT);
        dt.columns().add("height", DataColumn.Type.DOUBLE);
        dt.columns().add("sex", DataColumn.Type.TEXT);
        dt.columns().add("sal", DataColumn.Type.INT);


        dt.rows().add(new Object[]{1,"lilei","123456",20,1.83,"男",3000});
        DataRow drHmm = dt.rows().add(new Object[]{2, "韩梅梅", "666", 20,1.65,"女",3000});


        dt.rows().add(new Object[]{5,"张三","123456",20,1.83,5000});
        dt.rows().add(new Object[]{6,"李四","123456",30,1.73,"男",6000});
        dt.rows().add(new Object[]{7,"杜子腾","123456",30,1.76,"男",4500});
        dt.rows().add(new Object[]{8,"史珍香","123456",20,1.68,"女",5000});
        dt.rows().add(new Object[]{9,"王五","123456",20,1.83,"男",10000});
        dt.rows().add(new Object[]{10,"乔峰","123456",20,1.83,"男",8000});
        dt.rows().add(new Object[]{11,"段誉","123456",30,1.80,"男",10000});
        dt.rows().add(new Object[]{11,"小龙女","123456",30,1.63,"女",8000});
        dt.rows().add(new Object[]{11,"王语嫣","123456",30,1.63,"女",7000});

        return dt;
    }
    // 分组单列、单聚合函数 count
    public static void main1(String[] args) {

        DataTable dt = createTable();

        GroupBy groupBy = dt.groupBy("age");
        printGroupby(groupBy.getResult());

        Out.println("agg--------------");
        DataTable result = groupBy.count().getResult();
        printColumns(result);
        printDatas(result);


    }
    // 分组单列、单聚合函数max
    public static void main11(String[] args) {

        DataTable dt = createTable();

        GroupBy groupBy = dt.groupBy("age");
        printGroupby(groupBy.getResult());

        Out.println("agg--------------");
        DataTable result = groupBy.max("height").getResult();
        printColumns(result);
        printDatas(result);
    }
    // 分组单列、单聚合函数max，对多列求max
    public static void main(String[] args) {

        DataTable dt = createTable();

        GroupBy groupBy = dt.groupBy("age");
        printGroupby(groupBy.getResult());

        Out.println("agg--------------");
        DataTable result = groupBy
                .count()
                .max("sal")
                .max("height")
                .min("sal")
                .min("height")
                .sum("sal")
                .avg("sal")
                .getResult();
        printColumns(result);
        printDatas(result);
    }
    // 分组多列，单聚合函数
    public static void main2(String[] args) {

        DataTable dt = createTable();

        GroupBy groupBy = dt.groupBy("age","sex");
        printGroupby(groupBy.getResult());

        Out.println("agg--------------");
        DataTable result = groupBy.count().getResult();
        printColumns(result);
        printDatas(result);


    }
    // 分组多列，单聚合函数max()
    public static void main22(String[] args) {

        DataTable dt = createTable();

        GroupBy groupBy = dt.groupBy("age","sex");
        printGroupby(groupBy.getResult());

        Out.println("agg--------------");
        DataTable result = groupBy.max("height").getResult();
        printColumns(result);
        printDatas(result);


    }
//    分组单列，多聚合函数
    public static void main3(String[] args) {

        DataTable dt = createTable();

        GroupBy groupBy = dt.groupBy("age");
        printGroupby(groupBy.getResult());

        Out.println("agg--------------");
        DataTable groupByAgg = groupBy
                .count()
                .max("height")
                .getResult();
        printColumns(groupByAgg);
        printDatas(groupByAgg);
//        printAggGroupby();



    }
    //    分组多列，多聚合函数
    public static void main4(String[] args) {

        DataTable dt = createTable();

        GroupBy groupBy = dt.groupBy("age","sex");
        printGroupby(groupBy.getResult());

        Out.println("agg--------------");
        DataTable result = groupBy
                .count()
                .max("height")
                .getResult();
        printColumns(result);
        printDatas(result);


    }
    private static void printGroupby(List<GroupBy.Result> list){
        for(GroupBy.Result result : list){
            List<Object> columns = result.getColumns();
            List<DataRow> dataRows = result.getDataRows();

            Out.println("columns:",columns);
            Out.println("dataRows:",dataRows);
        }
    }

    private static void printAggGroupby(List<GroupByAgg.Result> list){
        for(GroupByAgg.Result result : list){
            List<Object> columns = result.getColumns();
            List<Object> data = result.getDatas();

            Out.println("columns:",columns);
            Out.println("dataRows:",data);
        }
    }
    static void printColumns(DataTable dt){
        Out.println("所有列、类型");
        for(int i = 0,size = dt.columns().count();i<size;i++){
            DataColumn item = dt.columns().get(i);
            Out.println(item.getName(),item.getType());
        }
    }

    static void printDatas(DataTable dt){
        Out.println("所有的数据：");
        for(int i = 0,size = dt.rows().count(); i<size ; i++){
            DataRow row = dt.rows().get(i);

            for(int j = 0,count = dt.columns().count();j<count;j++) {
                Out.print("", row.getValue(j),",");
            }
            Out.println();
        }
    }
}
